using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlServerCe;
using System.Data;

namespace it.mintlab.mobilenet.mintar
{
    /// <summary>
    /// From http://blogs.freshlogicstudios.com/Posts/View.aspx?Id=2a18bfe7-caec-467d-913c-597627e92247
    /// </summary>
    public class SqlServerCe20Utils
    {
        private static string connStr;

        public static string ConnStr
        {
            get { return SqlServerCe20Utils.connStr; }
            set { SqlServerCe20Utils.connStr = value; }
        }
    
        /// <summary>
        /// Get application setting by key
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public static string getAppSetting(string key)
        {
            SqlCeConnection conn = new SqlCeConnection(connStr);
            SqlCeDataReader reader = null;
            //string cmdStr = "SELECT TOP 1 value FROM app_setting WHERE [key] = '" + key.Replace("'", "''") + "'";
            string cmdStr = "SELECT value FROM app_setting WHERE [key] = '" + key.Replace("'", "''") + "'";
            //execute query
            try
            {
                conn.Open();
                SqlCeCommand cmd = new SqlCeCommand(cmdStr, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    return reader["value"].ToString();
                }
                return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                try { reader.Close(); }
                catch { }
                try { conn.Close(); }
                catch { }
            }
        }

        /// <summary>
        /// Set application setting by key
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public static void setAppSetting(string key, string value)
        {
            SqlCeConnection conn = new SqlCeConnection(connStr);
            string cmdStr = "UPDATE app_settings SET [value] = '" + value + "' WHERE [key] = '" + key.Replace("'", "''") + "'";
            //execute query
            try
            {
                conn.Open();
                SqlCeCommand cmd = new SqlCeCommand(cmdStr, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                try { conn.Close(); }
                catch { }
            }
        }

        public static DataTable setColumnsToString(DataTable source)
        {
            DataTable destination = source.Clone();
            foreach(DataColumn column in destination.Columns)
                if(!column.DataType.Equals(typeof(String)))
                    column.DataType = typeof(String);
            foreach (DataRow row in source.Rows)
                destination.ImportRow(row);
            
            return destination;
        }

        public static DataTable getAppSettings()
        {
            SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter("SELECT * FROM app_settings", SqlServerCe20Utils.connStr);
            DataTable dt = new DataTable();
            dataAdapter.Fill(dt);
            return dt;
        }
    }
}
